# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
import xlwt
import math
import cStringIO
from .. import format_common

from openerp.osv import fields, osv
from openerp.tools.translate import _
import time
from datetime import datetime
from mx import DateTime as dt
from mx.DateTime import RelativeDateTime as rdt
from datetime import timedelta
import base64
from xlwt import *

class param_account_statement_report(osv.osv_memory):
    _name = "param.account.statement.report"
    _description = "Statement of Account"

    _columns = {
        'date_to': fields.date("Date", required=True),
    }
    _defaults = {
        'date_to': lambda *a: time.strftime('%Y-%m-%d'),
    }
    
    def _build_contexts(self, cr, uid, ids, data, context=None):
        if context is None:
            context = {}
        result = {}
        partner_ids = ('ids' in data and data['ids']) or False
        model = ('model' in data and data['model']) or False

        result['date_to'] = data['form']['date_to']
        
        if partner_ids:
            result['partner_ids'] = partner_ids
        return result
    
    def generate(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        
        partner_comp_ids = []
        partner_ids = context.get('active_ids', [])
        
        for p in self.pool.get('res.partner').browse(cr, uid, partner_ids):
            if p.is_company == True and p.customer == True:
                partner_comp_ids.append(p.id)
        
        datas = {'ids': partner_comp_ids}
               
        datas['model'] = 'param.account.statement.report'
        datas['form'] = self.read(cr, uid, ids)[0]
        return {
            'type': 'ir.actions.report.xml',
            'report_name': 'account.statement.report_landscape',
            'datas': datas,
        }
    
    def print_report(self, cr, uid, ids, context=None):
        if context is None:
            context = {}
        data = {}
        data['ids'] = context.get('active_ids', [])
        data['model'] = context.get('active_model', 'ir.ui.menu')
        data['form'] = self.read(cr, uid, ids, ['date_to'], context=context)[0]
        for field in ['date_to']:
            if isinstance(data['form'][field], tuple):
                data['form'][field] = data['form'][field][0]   
        used_context = self._build_contexts(cr, uid, ids, data, context=context)

        return self._get_tplines_xls(cr, uid, ids, used_context, context=context)
    
    def _get_tplines_xls(self, cr, uid, ids, data, context):
        form = data
        if not ids:
            ids = data['ids']
        if not ids:
            return []
        
        user_obj    = self.pool.get('res.users')
        res_obj     = self.pool.get('res.partner')
        invoice_obj = self.pool.get('account.invoice')
        period_obj  = self.pool.get('account.period')
        dp_obj      = self.pool.get('decimal.precision')
        
        to_date = data['date_to'] 
        val = []
        
        workbook = xlwt.Workbook()
        M_header_tstyle = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=300)
        report_tstyle = format_common.font_style(position='left', bold=1, fontos='black', font_height=190)
        report_tstyle1 = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        header_tstyle = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        header_tstyle_c = format_common.font_style(position='center', bold=1, border=1, fontos='black', font_height=160)
        header_tstyle_l = format_common.font_style(position='left', bold=1,border=1, fontos='black', font_height=180)
        header_tstyle_c_Top = format_common.font_style(position='center', bold=1, border=4, fontos='black', font_height=180)
        header_tstyle_c_Bottom = format_common.font_style(position='center', bold=1, border=3, fontos='black', font_height=180)
        header_tstyle_r = format_common.font_style(position='right', bold=1,  border=1,fontos='black', font_height=180)
        view_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        view_tstyle_c_b = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        view_tstyle_l = format_common.font_style(position='left', fontos='black', font_height=180)
        view_tstyle_l_b = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        view_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        view_tstyle_r_b = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        other_tstyle = format_common.font_style(position='left', fontos='black', font_height=180)
        other_tstyle_c = format_common.font_style(position='center', fontos='black', font_height=180)
        other_tstyle_r = format_common.font_style(position='right', fontos='black', font_height=180)
        text_tstyle = format_common.font_style(position='left', bold=1, border=1, fontos='black', font_height=180)
        text_tstyle1 = format_common.font_style(position='left', bold=1, fontos='black', font_height=220)
        text_tstyle2 = format_common.font_style(position='left', fontos='black', font_height=220)
        text_tstyle_c = format_common.font_style(position='center', bold=1, fontos='black', font_height=180)
        text_tstyle_l = format_common.font_style(position='left', bold=1, fontos='black', font_height=180)
        text_tstyle_r = format_common.font_style(position='right', bold=1, fontos='black', font_height=180)
        text_tstyle_r_closing = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        text_tstyle_Top = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Bottom = format_common.font_style(position='left', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_Top_r = format_common.font_style(position='right', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_l = format_common.font_style(position='left', bold=1, border=4, fontos='black', font_height=180)
        text_tstyle_Top_r_r = format_common.font_style(position='right', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Top_l_l = format_common.font_style(position='left', bold=1, border=6, fontos='black', font_height=180)
        text_tstyle_Bottom_r = format_common.font_style(position='right', bold=1, border=3, fontos='black', font_height=180)
        text_tstyle_TopBottom = format_common.font_style(position='left', bold=1, border=2, fontos='black', font_height=180)
        text_tstyle_TopBottom_r = format_common.font_style(position='right', bold=1, border=2, fontos='black', font_height=180)
        borders_noRB_tstyle = format_common.font_style(position='left', bold=1,border=9, fontos='black', font_height=180)
        borders_noLRB_tstyle_r = format_common.font_style(position='right', bold=1,border=6, fontos='black', font_height=180)
        borders_noLRT_tstyle_bold_r = format_common.font_style(position='right', bold=1,border=5, fontos='black', font_height=180)
        borders_noLB_tstyle_r = format_common.font_style(position='right', bold=1,border=8, fontos='black', font_height=180)
        borders_noRT_tstyle = format_common.font_style(position='left', bold=1,border=11, fontos='black', font_height=180)
        borders_noLT_tstyle_bold_r = format_common.font_style(position='right', bold=1,border=10, fontos='black', font_height=180)
        borders_none_tstyle_r = format_common.font_style(position='right', bold=1,border=12, fontos='black', font_height=180)
        borders_noL_tstyle_r = format_common.font_style(position='right', bold=1,border=13, fontos='black', font_height=180)
        borders_noLR_tstyle = format_common.font_style(position='right', bold=1,border=7, fontos='black', font_height=180)
        borders_noLRT_tstyle_r = format_common.font_style(position='right', border=5, fontos='black', font_height=180)
        borders_noLT_tstyle_r = format_common.font_style(position='right', border=10, fontos='black', font_height=180)
        
        dp_data = {}
        decimal_format = easyxf(num_format_str = '0.000000')
        dp_ids = dp_obj.search(cr, uid, [])

        for dp_id in dp_obj.browse(cr, uid, dp_ids, context=None) :
            dp_data[dp_id.name] = dp_id.digits
        
        dp_account = dp_data['Account']
        
        account_digits = int(math.pow(10, dp_account))
        digit_account_output = '#,##0.' + str(account_digits)[1:]
        account_format = easyxf(num_format_str = digit_account_output)
        
        account_format_bold = easyxf('font: bold 1')
        account_format_bold.num_format_str = digit_account_output
        
        account_format_bold_t = easyxf('font: bold 1;' "borders: top thin")
        account_format_bold_t.num_format_str = digit_account_output
        
        account_format_bold_tr = easyxf('font: bold 1;' "borders: top thin, right thin")
        account_format_bold_tr.num_format_str = digit_account_output
        
        account_format_bold_b = easyxf('font: bold 1;' "borders: bottom thin")
        account_format_bold_b.num_format_str = digit_account_output
        
        account_format_bold_br = easyxf('font: bold 1;' "borders: bottom thin, right thin")
        account_format_bold_br.num_format_str = digit_account_output
        
        account_format_tb = easyxf("borders: top thin, bottom thin")
        account_format_tb.num_format_str = digit_account_output
        
        account_format_tbr = easyxf("borders: top thin, bottom thin, right thin")
        account_format_tbr.num_format_str = digit_account_output
        
        sheet = workbook.add_sheet('SOA Report XLS')
        sheet.row(0).height = 350
        row = 0
        
        sheet.col(0).width = 256 * 22
        sheet.col(1).width = 256 * 13
        sheet.col(2).width = 256 * 15
        sheet.col(3).width = 256 * 15
        sheet.col(4).width = 256 * 15
        sheet.col(5).width = 256 * 15
        sheet.col(6).width = 256 * 15
        
        company_name = user_obj.browse(cr, uid, uid).company_id.name
        sheet.write_merge(row, row, 0, 6, company_name, report_tstyle1)
        
        row += 1
        sheet.write_merge(row, row, 0, 6, 'Statement of Account', report_tstyle1)
        
        period_ids = self.pool.get('account.period').search(cr, uid, [('date_start','<=',to_date)], order='date_start DESC')
        if period_ids:
            period = self.pool.get('account.period').browse(cr, uid, period_ids[0])
        
        company = self.pool.get('res.company').browse(cr, uid, (self.pool.get('res.users').browse(cr, uid, uid).company_id.id))
        partner_ids = form['partner_ids'] or False
        partner_comp_ids = []
        
        for part in self.pool.get('res.partner').browse(cr, uid, partner_ids):
            if part.is_company == True and part.customer == True:
                partner_comp_ids.append(part.id)
        
        for partner in res_obj.browse(cr, uid, partner_comp_ids):
            
            name = (partner.name and str(partner.name)) or ''
            street = (partner.street and str(partner.street)) or ''
            street2 = (partner.street2 and str(partner.street2)) or ''
            zip = (partner.zip and str(partner.zip + ' ' + partner.city and str(partner.city))) or ''
            country = (partner.country_id and partner.country_id.name and str(partner.country_id.name)) or ''
            payment_term = partner.property_payment_term.name or ''
            sign = 1
            max_period = period.id or False
            date_to = max_period and period_obj.browse(cr, uid, max_period).date_stop or False
            period_1 = period_2 = period_3 = False
            date_start_max_period = max_period and period_obj.browse(cr, uid, max_period).date_start or False
            val_period = []
            val_period.append(('special', '=', False))
            if date_start_max_period:
                val_period.append(('date_start', '<=', date_start_max_period))
     
            qry_period_ids = period_obj.search(cr, uid, val_period, order='date_start DESC')
            if qry_period_ids[1]:
                period_1 = qry_period_ids[1]
            if qry_period_ids[2]:
                period_2 = qry_period_ids[2]
            if qry_period_ids[3]:
                period_3 = qry_period_ids[3]
     
            if date_to:
                cr.execute(
                        "select aml.is_depo as deposit, ai.id as invoice_id, aml.period_id as period_id, aml.date_maturity as due_date, ai.payment_term as term_id, aml.id as aml_id, am.name as inv_name, aml.date as inv_date, ai.name as inv_ref, rp.name as sales_name, aml.debit - aml.credit as home_amt, " \
                        "abs(CASE WHEN (aml.currency_id is not null) THEN amount_currency ELSE aml.debit - aml.credit END) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) " \
                        "as inv_amt, " \
                        "abs(coalesce ( " \
                        "(select sum(aml2.debit - aml2.credit) from account_move_line aml2 where aml2.reconcile_partial_id = aml.reconcile_partial_id and aml2.id != aml.id and aml2.date  <= '" +str(date_to) + "'), " \
                        "(select sum(aml3.debit - aml3.credit) from account_move_line aml3 where aml3.reconcile_id = aml.reconcile_id and aml3.id != aml.id and aml3.date  <= '" +str(date_to) + "'), " \
                        "0)) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) as paid_home, " \
                        "abs(coalesce ( " \
                        "(select sum(CASE WHEN (aml4.currency_id is not null) THEN amount_currency ELSE aml4.debit - aml4.credit END) from account_move_line aml4 where aml4.reconcile_partial_id = aml.reconcile_partial_id and aml4.id != aml.id and aml4.date  <= '" +str(date_to) + "'), " \
                        "(select sum(CASE WHEN (aml5.currency_id is not null) THEN amount_currency ELSE aml5.debit - aml5.credit END) from account_move_line aml5 where aml5.reconcile_id = aml.reconcile_id and aml5.id != aml.id and aml5.date  <= '" +str(date_to) + "'), " \
                        "0)) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END) as paid " \
                        "from account_move_line aml " \
                        "left join account_move am on aml.move_id = am.id left join account_invoice ai on am.id = ai.move_id " \
                        "left join account_account aa on aml.account_id = aa.id left join account_journal aj on am.journal_id = aj.id " \
                        "left join res_users rs on rs.id = ai.user_id " \
                        "left join res_partner rp on rs.partner_id = rp.id where aml.partner_id IS NOT NULL " \
                        "and am.state IN ('draft', 'posted')  " \
                        "and aa.type = 'receivable' " \
                        "And not (aml.debit > 0 and aml.is_depo = False and aj.type in ('cash', 'bank')) " \
                        "and abs((aml.debit - aml.credit) - (abs(coalesce ( " \
                        "(select sum(aml2.debit - aml2.credit) from account_move_line aml2 where aml2.reconcile_partial_id = aml.reconcile_partial_id and aml2.id != aml.id and aml2.date  <= '" +str(date_to) + "'), " \
                        "(select sum(aml3.debit - aml3.credit) from account_move_line aml3 where aml3.reconcile_id = aml.reconcile_id and aml3.id != aml.id and aml3.date  <= '" +str(date_to) + "'), 0 " \
                        ")) * (CASE WHEN (debit - credit) > 0 THEN 1 ELSE -1 END))) > 0 " \
                        "And aml.date  <= '" +str(date_to) + "' "\
                        "and not (aj.type in ('bank', 'cash') and aml.is_depo = False) " \
                        "and aml.partner_id = " + str(partner.id) + " order by aml.date")
                
                qry3 = cr.dictfetchall()
                
                current = due_1 = due_2 = due_3 = due_4 = 0.00
                total_debit = total_credit = total_balance = 0.00
                total_amt1 = total_amt2= total_amt3 = total_amt4 = total_home_amt1 = total_home_amt2 = total_home_amt3 = total_home_amt4 = 0
                
                row += 1
                sheet.write(row, 0, '   ' + name, view_tstyle)
                sheet.write(row, 4, 'Page', view_tstyle)
                sheet.write(row, 5, ': 1', view_tstyle)
            
                row += 1
                if street:
                    sheet.write_merge(row, row, 0, 3, '   ' + street, view_tstyle)
                sheet.write(row, 4, 'Payment Term', view_tstyle)
                sheet.write(row, 5, ': ' + str(payment_term), view_tstyle)
                
                row += 1
                if street2:
                    sheet.write_merge(row, row, 0, 3, '   ' + street2, view_tstyle)
                sheet.write(row, 5, 'IN - Invoice', view_tstyle)
                
                row += 1
                if zip:
                    sheet.write_merge(row, row, 0, 3, '   ' + zip, view_tstyle) 
                sheet.write(row, 5, 'RF - Refund', view_tstyle)
                
                row += 1
                if country:
                    sheet.write_merge(row, row, 0, 3, '   ' + country, view_tstyle)
                sheet.write(row, 5, 'DP - Deposit', view_tstyle)
                
                row += 2
                sheet.write(row, 0, 'Statement As At : ', view_tstyle_l)
                sheet.write(row, 1, str(to_date), view_tstyle_c_b)
                sheet.write(row, 2, 'Deposit : ', view_tstyle_r)
                sheet.write(row, 3, (partner.depo_credit - partner.depo_debit), account_format_bold)
                sheet.write(row, 4, 'Currency : ', view_tstyle_r)
                sheet.write(row, 5, partner.company_id.currency_id.name, view_tstyle_c_b)
                
                row += 1
                sheet.write(row, 0, 'Invoice / Deposit No.', header_tstyle_l)
                sheet.write(row, 1, 'TP', header_tstyle_c)
                sheet.write(row, 2, 'Cust PO No', header_tstyle_c)
                sheet.write(row, 3, 'Invoice Date', header_tstyle_c)
                sheet.write(row, 4, 'Due Date', header_tstyle_r)
                sheet.write(row, 5, 'Debit', header_tstyle_r)
                sheet.write(row, 6, 'Credit', header_tstyle_r)
                
                if qry3:
                    for t in qry3:        
                        invoice_id = invoice_name = ''
                        invoice_date = due_date = False
                        
                        sale_term_id = t['term_id']
                        daysremaining = 0
                        d = datetime.strptime(t['inv_date'], '%Y-%m-%d')
                        delta = datetime.strptime(date_to, '%Y-%m-%d') - d
                        daysremaining = delta.days
                        cust_po_no = ''
                        
                        invoice_id = t['invoice_id'] or '',
                        invoice_name = str(t['inv_name']) or '',
                        invoice_date = str(t['inv_date']) or False,
                        due_date = str(t['due_date']) or False,
                        
                        remain_amt = (t['inv_amt'] * sign) - (t['paid'] * sign)

                        type = ''
                        deposit = t['deposit'] or 0.00
                        debit = ((remain_amt > 0) and remain_amt) or 0.00
                        credit = ((remain_amt < 0) and (remain_amt * -1)) or 0.00
                        if deposit:
                            type = 'DP'
                        else:
                            remain = debit - credit
                            if remain > 0:
                                type = 'IN'
                            else:
                                type = 'CN'
                                     
                        total_debit += ((remain_amt > 0) and remain_amt) or 0.00
                        total_credit += ((remain_amt < 0) and (remain_amt * -1)) or 0.00
                        total_balance = total_debit - total_credit
                         
                        if t['period_id'] == max_period:
                            current += remain_amt
                        elif t['period_id'] == period_1:
                            due_1 += remain_amt
                        elif t['period_id'] == period_2:
                            due_2 += remain_amt
                        elif t['period_id'] == period_3:
                            due_3 += remain_amt
                        else:
                            due_4 += remain_amt
                        
                        
                        row += 1
                        sheet.write(row, 0, invoice_name, view_tstyle_l)
                        sheet.write(row, 1, type, view_tstyle_c)
                        sheet.write(row, 2, cust_po_no, view_tstyle_c)
                        sheet.write(row, 3, invoice_date, view_tstyle_c)
                        sheet.write(row, 4, due_date, view_tstyle_c)
                        sheet.write(row, 5, debit, account_format)
                        sheet.write(row, 6, credit, account_format)
                            
                    row += 1
                    sheet.write_merge(row, row, 0, 4, 'Cummulative Total', borders_noRB_tstyle)
                    sheet.write(row, 5, total_debit, account_format_bold_t)
                    sheet.write(row, 6, total_credit, account_format_bold_tr)
                    
                    row += 1
                    sheet.write_merge(row, row, 0, 4, 'Balance', borders_noRT_tstyle)
                    sheet.write(row, 5, total_balance, account_format_bold_b)
                    sheet.write(row, 6, '', account_format_bold_br)

                    row += 1
                    sheet.write(row, 0, '', borders_noLRB_tstyle_r)
                    sheet.write_merge(row, row, 1, 2, 'Aged by Due Date', borders_noLRB_tstyle_r)
                    sheet.write_merge(row, row, 3, 6, '', borders_noLB_tstyle_r)
                    
                    row += 1
                    sheet.write(row, 0, '')
                    sheet.write_merge(row, row, 1, 2, 'Current (Not Due)', borders_noLRT_tstyle_r)
                    sheet.write(row, 3, '1 To 30', borders_noLRT_tstyle_r)
                    sheet.write(row, 4, '31 To 60', borders_noLRT_tstyle_r)
                    sheet.write(row, 5, '61 To 90', borders_noLRT_tstyle_r)
                    sheet.write(row, 6, 'Over 90', borders_noLT_tstyle_r)
                    
                    row += 1
                    sheet.write(row, 0, '', borders_noLRT_tstyle_r)
                    sheet.write_merge(row, row, 1, 2, current, account_format_tb)
                    sheet.write(row, 3, due_1, account_format_tb)
                    sheet.write(row, 4, due_2, account_format_tb)
                    sheet.write(row, 5, due_3, account_format_tb)
                    sheet.write(row, 6, due_4, account_format_tbr)
                    row += 2
            
        row += 1
        sheet.write_merge(row, row, 0, 6, 'End of Report', text_tstyle_Top_l) 
        
        stream = cStringIO.StringIO()
        workbook.save(stream)
            
        cr.execute(""" DELETE FROM output""")
        attach_id = self.pool.get('output').create(cr, uid, {'name':'Statement of Account Report.xls', 'xls_output':base64.encodestring(stream.getvalue())})
                           
        return {
                'name': _('Notification'),
                'context': context,
                'view_type': 'form',
                'view_mode': 'form',
                'res_model': 'output',
                'res_id':attach_id,
                'type': 'ir.actions.act_window',
                'target':'new'
                }

# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: